﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace AISFMIOperator
{
    class City
    {
        public City()
        { }

        public int CityID
        { get; set; }

        public string CityName
        { get; set; }

        public string CityFullName
        {
            get { return CityName + " (" + Region.RegionName + ")"; }
        }

        public Region Region
        { get; set; }

        public void Save()
        {
            SqlCommand command = new SqlCommand("CreateCity");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@city_name", SqlDbType.VarChar, 50).Value = CityName;
            command.Parameters.Add("@region_id", SqlDbType.Int).Value = Region.RegionID;
            command.Parameters.Add("@city_id", SqlDbType.Int).Direction = ParameterDirection.Output;
            SQLDBLayer.ExecSPNonQuery(command);
            this.CityID = (int)command.Parameters["@city_id"].Value;
        }

        public void Update()
        {
            SqlCommand command = new SqlCommand("UpdateCity");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@city_name", SqlDbType.VarChar, 50).Value = CityName;
            command.Parameters.Add("@region_id", SqlDbType.Int).Value = Region.RegionID;
            command.Parameters.Add("@city_id", SqlDbType.Int).Value = CityID;
            SQLDBLayer.ExecSPNonQuery(command);
        }

        public void Delete()
        {
            SqlCommand command = new SqlCommand("DeleteCity");
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@city_id", SqlDbType.Int).Value = CityID;
            SQLDBLayer.ExecSPNonQuery(command);
        }

        public static List<City> GetAllCities()
        {
            DataTable table = new DataTable();
            List<City> cities = new List<City>();
            SqlCommand command = new SqlCommand("GetCities");
            command.CommandType = CommandType.StoredProcedure;

            table = SQLDBLayer.ExecSPTable(command);

            foreach (DataRow row in table.Rows)
            {
                City city = new City();
               
                city.CityID = (int)row["city_id"];
                int region_id = (int)row["region_id"];
                city.CityName = row["city_name"].ToString();

                city.Region = (Region)Region.GetAllRegions().Where(o => o.RegionID == region_id).ToList()[0];

                cities.Add(city);
            }

            return cities;
        }
    }
}
